﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zhy.DigitaPlatform.Entities;
using Zhy.DigitaPlatform.IDataAccess;

namespace Zhy.DigitaPlatform.DataAccess
{
    public class LocalDataAccess : ILocalDataAccess
    {
        #region 基础方法
        // Sqlite的数据处理对象
        /// <summary>
        /// 建立连接
        /// </summary>
        private SQLiteConnection conn = null;// 建立连接
        /// <summary>
        /// 执行SQL
        /// </summary>
        private SQLiteCommand comm = null;// 执行SQL
        /// <summary>
        /// 填充数据列表
        /// </summary>
        private SQLiteDataAdapter adapter = null;// 填充数据列表
        /// <summary>
        /// //事务
        /// </summary>
        private SQLiteTransaction trans = null;

        /// <summary>
        /// Sqlite数据库的连接字符串
        /// </summary>
        string connStr = "Data Source=Platform.db;Version=3;";
        /// <summary>
        /// 执行查询操作
        /// 执行一个SQL语句，并将结果通过DataTable返回
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private DataTable GetDatas(string sql, Dictionary<string, object> param = null)
        {
            try
            {
                if (conn == null)
                    conn = new SQLiteConnection(connStr);
                conn.Open();

                adapter = new SQLiteDataAdapter(sql, conn);

                if (param != null)
                {
                    List<SQLiteParameter> parameters = new List<SQLiteParameter>();
                    foreach (var item in param)
                    {
                        parameters.Add(
                            new SQLiteParameter(item.Key, DbType.String)
                            { Value = item.Value }
                        );
                        adapter.SelectCommand.Parameters.Add(new SQLiteParameter(item.Key, DbType.String)
                        { Value = item.Value });
                    }
                    //adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
                }
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);

                return dataTable;
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                this.Dispose();
            }
        }

        /// <summary>
        /// 执行更新插入等操作
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        private int SqlExecute(List<string> sqls)
        {
            int count = 0;
            try
            {
                if (conn == null)
                    conn = new SQLiteConnection(connStr);
                conn.Open();

                trans = conn.BeginTransaction();
                foreach (var sql in sqls)
                {
                    comm = new SQLiteCommand(sql, conn);
                    count += comm.ExecuteNonQuery();
                }
                trans.Commit();
                return count;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                this.Dispose();
            }

        }

        /// <summary>
        /// 关闭连接资源释放
        /// </summary>
        private void Dispose()
        {
            if (trans != null)
            {
                trans.Dispose();
                trans = null;
            }
            if (adapter != null)
            {
                adapter.Dispose();
                adapter = null;
            }
            if (comm != null)
            {
                comm.Dispose();
                comm = null;
            }
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }
        #endregion


        #region 登录逻辑
        public DataTable Login(string username, string password)
        {
            // 不能拼接 ，Sql注入攻击
            string userSql = "select * from sys_users where user_name=@user_name and password=@password";

            Dictionary<string, object> param = new Dictionary<string, object>();
            param.Add("@user_name", username);
            param.Add("@password", password);

            DataTable dataTable = this.GetDatas(userSql, param);
            if (dataTable.Rows.Count == 0)
                throw new Exception("用户名或密码错误");

            return dataTable;
        }
        #endregion

        #region 设备操作
        /// <summary>
        /// 保存设备信息
        /// </summary>
        /// <param name="devices"></param>
        /// <returns></returns>
        public void SaveDevice(List<DeviceEntity> devices)
        {
            try
            {
                int count = 0;
                conn = new SQLiteConnection(connStr);
                conn.Open();
                trans = conn.BeginTransaction();

                string sql = $"delete from devices;delete from device_properties;delete from variables;";

                comm = new SQLiteCommand(sql, conn);
                comm.ExecuteNonQuery();//删除

                foreach (var item in devices)
                {
                    sql = $"insert into devices(d_num,x,y,z,w,h,d_type_name,header,flow_direction,rotate) values('{item.DeviceNum}','{item.X}','{item.Y}','{item.Z}','{item.W}','{item.H}','{item.DeviceTypeName}','{item.Header}','{item.FlowDirection}','{item.Rotate}')";
                    comm.CommandText = sql;
                    var flag = comm.ExecuteNonQuery();// 插入
                    count += flag;

                    // 保存对应的属性
                    // 属性
                    sql = $"delete from device_properties where d_num={item.DeviceNum}";
                    comm.CommandText = sql;
                    comm.ExecuteNonQuery();
                    foreach (var p in item.Props)
                    {
                        if (string.IsNullOrEmpty(p.PropName) || string.IsNullOrEmpty(p.PropValue)) continue;

                        sql = $"insert into device_properties(d_num,prop_name,prop_value) values('{item.DeviceNum}','{p.PropName}','{p.PropValue}')";
                        comm.CommandText = sql;
                        comm.ExecuteNonQuery();// 插入
                    }
                    // 保存对应的点位信息
                    // 清除点位前,需要将报警条件\联控条件清除,再重新添加
                    sql = $"delete from variables where d_num={item.DeviceNum}";
                    comm.CommandText = sql;
                    comm.ExecuteNonQuery();
                    foreach (var v in item.Vars)
                    {
                        if (string.IsNullOrEmpty(v.Header) || string.IsNullOrEmpty(v.Address)) continue;

                        sql = $"insert into variables(d_num,v_num,header,address,offset,modulus,v_type) values('{item.DeviceNum}','{v.VarNum}','{v.Header}','{v.Address}','{v.Offset}','{v.Modulus}','{v.VarType}')";
                        comm.CommandText = sql;
                        comm.ExecuteNonQuery();// 插入
                    }
                    // 对应的报警  需要同步删除
                }
                if (count != devices.Count)
                    throw new Exception("设备数据未完全保存");

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                this.Dispose();
            }
        }

        /// <summary>
        /// 获取设备信息
        /// </summary>
        /// <returns></returns>
        public List<DeviceEntity> GetDevices()
        {
            // 获取设备信息
            string sql = "select * from devices";
            DataTable dt_device = this.GetDatas(sql, null);
            // 获取设备属性
            sql = $"select * from device_properties";
            DataTable dt_props = this.GetDatas(sql, null);
            // Modbus属性-变量
            sql = $"select * from variables";
            DataTable dt_vars = this.GetDatas(sql, null);

            // 将三个表的数据整合到一起
            var result = dt_device.AsEnumerable().Select(d =>
            {
                return new DeviceEntity()
                {
                    DeviceNum = d["d_num"].ToString(),
                    X = d["x"].ToString(),
                    Y = d["y"].ToString(),
                    Z = d["z"].ToString(),
                    W = d["w"].ToString(),
                    H = d["h"].ToString(),
                    DeviceTypeName = d["d_type_name"].ToString(),
                    Header = d["header"].ToString(),
                    FlowDirection = d["flow_direction"].ToString(),
                    Rotate = d["rotate"].ToString(),

                    Props = dt_props.AsEnumerable().Where(dp => dp["d_num"].ToString() == d["d_num"].ToString())
                            .Select(dp => new DevicePropItemEntity()
                            {
                                PropName = dp["prop_name"].ToString(),
                                PropValue = dp["prop_value"].ToString()
                            }).ToList(),

                    Vars = dt_vars.AsEnumerable().Where(dv => dv["d_num"].ToString() == d["d_num"].ToString())
                            .Select(dv => new VariableEntity()
                            {
                                VarNum = dv["v_num"].ToString(),
                                Header = dv["header"].ToString(),
                                Address = dv["address"].ToString(),
                                Offset = double.Parse(dv["offset"].ToString()),
                                Modulus = double.Parse(dv["modulus"].ToString()),
                                VarType = dv["v_type"].ToString()
                            }).ToList()
                };
            });

            return result.ToList();
        }
        #endregion

        #region 获取组件信息
        /// <summary>
        /// 获取组件信息
        /// </summary>
        /// <returns></returns>
        public List<ThumbEntity> GetThumbList()
        {
            string sql = "select * from thumbs";
            DataTable dt = this.GetDatas(sql, null);

            var result = from d in dt.AsEnumerable()
                         select new ThumbEntity
                         {
                             Icon = d["icon"].ToString(),
                             Header = d["header"].ToString(),
                             TargetType = d["target_type"].ToString(),
                             Width = int.Parse(d["w"].ToString()),
                             Height = int.Parse(d["h"].ToString()),
                             Category = d["category"].ToString()
                         };

            return result.ToList();
        }
        #endregion

        /// <summary>
        /// 获取参数配置数据
        /// </summary>
        /// <returns></returns>
        public List<PropEntity> GetPropertyOption()
        {
            string sql = "select * from properties";
            DataTable dt1 = this.GetDatas(sql, null);

            var result = (from q1 in dt1.AsEnumerable()
                          select new PropEntity
                          {
                              PropHeader = q1["p_header"].ToString(),
                              PropName = q1["p_name"].ToString(),
                              PropType = int.Parse(q1["p_type"].ToString())
                          }).ToList();

            return result;
        }
    }
}
